
import json

# import file_util
try:
    from top.starp.util import file_util
    from top.starp.util import k
    from top.starp.util import common
    from top.starp.util import json_util
except Exception as e:
    print(e)
    import file_util
    import list_util
import pymysql

from pypika import Query, Table, Field

# Query.from_(Table('users')).select('username').where(Field('id') == 42)



def get_all_cols(connection,db_name=None):

    """

    获取数据库中的所有表

    """
    # host='localhost'
    # user='root'
    # password="123456"
    # db="public"
    # db="fastlink"
    # user='用户名',
    # connection = pymysql.connect(host=host,
    # user=user,

    # password=password,

    # db=db_name,
    # # db=‘数据库名称’,
    # charset='utf8',
    # cursorclass=pymysql.cursors.DictCursor)

    # connection = pymysql.connect(host=host,

    # user='用户名',

    # password='密码',

    # db='数据库名称',
    # # db=‘数据库名称’,

    # charset='utf8',

    # cursorclass=pymysql.cursors.DictCursor)

    tables = []
    try:
        with connection.cursor() as cursor:
            # sql = '''SHOW TABLES''' # 同理 改成 '''SHOW DATABASES''' 即可获取所有数据库名称
            # sql =f"""
            # select * from information_schema.TABLES where TABLE_SCHEMA='{db_name}' ;

            # """
            # sql = f"""
            # select COLUMN_NAME from information_schema.COLUMNS where table_name = '{table_name}'
            # """
            # t_exam_paper_answer
            # table_name='t_exam_paper_answer'
            WHERE_TABLE_SCHEMA=f" WHERE TABLE_SCHEMA = '{db_name}'  "
            if db_name is None:
                WHERE_TABLE_SCHEMA=""
            
            sql = f"""
            select * from information_schema.COLUMNS {WHERE_TABLE_SCHEMA} 
            """
            # print("sql")
            # print(sql)
            cursor.execute(sql)
            table = cursor.fetchall() # 获取所有（输出是列表，列表中的每个元素是字典，字典的KEY是Tables_in_拼接数据库名称）
            for k in range(len(table)): # 提取表名
                # tables.append(table[k]['Tables_in_数据库名称'])
                tables.append(table[k])
    except Exception as e:
        print(e)
        print("sql")
        print(sql)
    finally:
        connection.commit()
        connection.close()

    return tables


def lastDay(your_date_column,dayDiff=1):
    return   f"""
    {your_date_column} = CURDATE() - INTERVAL {dayDiff} DAY
    """

def last周五Oracle(col_name):
    return f"""

    DATEPART(weekday, {col_name}) = 6
    AND {col_name} >= DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 0)
    AND {col_name} < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)

    """

# your_date_column = CURDATE() - INTERVAL 1 DAY
# or

def last周五or前一天(col_name):
    return f"""
    {last周五(col_name)}
    or
    {lastDay(col_name,dayDiff=1)}
    """
    
    # return    last周五Mysql(col_name)

def last周五(col_name):
    return    last周五Mysql(col_name)

#     return f"""
#     DATEPART(weekday, {col_name}) = 6
#   AND {col_name} >= DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 0)
#   AND {col_name} < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
#     """
    # return f"""

    # DATEPART(weekday, {col_name}) = 6
    # AND {col_name} >= DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 0)
    # AND {col_name} < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
    # 
    # """

def last周五Mysql(col_name):

#     return f"""
#     DATEPART(weekday, {col_name}) = 6
#   AND {col_name} >= DATEADD(week, DATEDIFF(week, 0, GETDATE()) - 1, 0)
#   AND {col_name} < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
#     """
    return f"""

 DAYOFWEEK({col_name}) = 6
  AND {col_name}>= DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) + 3 DAY)
  AND {col_name}< DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) - 4 DAY)

  
    """


def select(sql,connection,):
    resList=[]
    try:
        with connection.cursor() as cursor:
            # sql = '''SHOW TABLES''' # 同理 改成 '''SHOW DATABASES''' 即可获取所有数据库名称
            # sql =f"""
            # select * from information_schema.TABLES where TABLE_SCHEMA='{db_name}' ;

            # """
            # sql = f"""
            # select COLUMN_NAME from information_schema.COLUMNS where table_name = '{table_name}'
            # """
            # t_exam_paper_answer
            # table_name='t_exam_paper_answer'
            # sql = f"""
            # select * from information_schema.COLUMNS WHERE TABLE_SCHEMA = '{db_name}' 
            # """
            cursor.execute(sql)
            table = cursor.fetchall() # 获取所有（输出是列表，列表中的每个元素是字典，字典的KEY是Tables_in_拼接数据库名称）
            for k in range(len(table)): # 提取表名
                # tables.append(table[k]['Tables_in_数据库名称'])
                resList.append(table[k])
    except Exception as e:
        print(e)
    finally:
        connection.commit()
        connection.close()

    return resList

def getConnectionByConfPath(confPath):
    conf=json_util.file_path_to_dict(confPath)
    return   getConnection(conf=conf)


def getConnection(conf):
    # common.mongodb_conf_path
    host=conf.get(k.host,"localhost")
    username=conf.get(k.username)
    password=conf.get(k.password)
    db_name=conf.get(k.db_name,"public")
    port=conf.get(k.port,"3306")
    port=int(port)
    connection = pymysql.connect(host=host,
        port=port,
        user=username,
        password=password,
        db=db_name,
        # db=‘数据库名称’,
        charset='utf8',
        cursorclass=pymysql.cursors.DictCursor)
    return connection


if __name__ == "__main__":
    # csv_json()
    pass
    conf=json_util.file_path_to_dict(common.mysql_conf_path_baian)
    # conf.get(k.host)
    # dir_name=rf'D:\file\图书联盟'
    # csv_out_path=rf"/file/csvcsv图书联盟/图书联盟.csv"
    # lst=read_json_files(dir_name)
    # json_to_csv()
    # json_list_to_csv(lst,csv_out_path)
    # file_util.to_csv(lst,csv_out_path)
